﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace BO
{
    /// <summary>
    /// Summary description for VYC09BO: Khach hang
    /// </summary>
    public class VYC09BO : DBConnectionBO
    {
        public VYC09BO()
            : base()
        {

        }

        /// <summary>
        /// Lay danh sach cac du lieu trong VYC04
        /// </summary>
        /// <returns></returns>
        public List<ModelVYC09> GetAllList()
        {
            var list = new List<ModelVYC09>();
            const string query = "SELECT [Id],[IdLoaiKhachHang],[Ten],[CapBac],[Username],[Password],[PasswordHash],[Email],[SDT],[DiaChi],[TongDiem],[NgayCapNhat],[TrangThai] FROM [VYC-09]";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var model = new ModelVYC09();

                model.Id = (dataTable.Rows[i][0] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][0]);
                model.IdLoaiKhachHang = (dataTable.Rows[i][1] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][1]);
                model.Ten = (dataTable.Rows[i][2] == DBNull.Value) ? string.Empty : dataTable.Rows[i][2].ToString();
                model.CapBac = (dataTable.Rows[i][3] == DBNull.Value) ? string.Empty : dataTable.Rows[i][3].ToString();
                model.Username = (dataTable.Rows[i][4] == DBNull.Value) ? string.Empty : dataTable.Rows[i][4].ToString();
                model.Password = (dataTable.Rows[i][5] == DBNull.Value) ? string.Empty : dataTable.Rows[i][5].ToString();
                model.PasswordHash = (dataTable.Rows[i][6] == DBNull.Value) ? string.Empty : dataTable.Rows[i][6].ToString();
                model.Email = (dataTable.Rows[i][7] == DBNull.Value) ? string.Empty : dataTable.Rows[i][7].ToString();
                model.SDT = (dataTable.Rows[i][8] == DBNull.Value) ? string.Empty : dataTable.Rows[i][8].ToString();
                model.DiaChi = (dataTable.Rows[i][9] == DBNull.Value) ? string.Empty : dataTable.Rows[i][9].ToString();
                model.TongDiem = (dataTable.Rows[i][10] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][10]);
                model.NgayCapNhat = (dataTable.Rows[i][11] == DBNull.Value) ? DateTime.Now : (DateTime)dataTable.Rows[i][11];
                model.TrangThai = (dataTable.Rows[i][12] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][12].ToString());

                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// Add new a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddData(ModelVYC09 model)
        {
            var query = "INSERT INTO [VYC-09]([IdLoaiKhachHang],[Ten],[CapBac],[Username],[Password],[PasswordHash],[Email],[SDT],[DiaChi],[TongDiem],[NgayCapNhat],[TrangThai]) VALUES (" + model.IdLoaiKhachHang +
                        "N'" + model.Ten + "', N'" + model.CapBac + "', '" + model.Username + "', '" + model.Password + "', '"
                        + model.PasswordHash + "', '" + model.Email + "', '" + model.SDT + "', N'" + model.DiaChi + "', "
                        + model.TongDiem + ", GETDATE(), " + model.TrangThai + ")";
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Edit a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int EditData(ModelVYC09 model)
        {
            var query = "UPDATE [VYC-09] SET " +
                        "[IdLoaiKhachHang] = " + model.IdLoaiKhachHang +
                        ",[Ten] = N'" + model.Ten +
                        ",[CapBac] = N'" + model.CapBac + "'" +
                        ",[Username] = '" + model.Username + "'" +
                        ",[Password] = '" + model.Password + "'" +
                        ",[PasswordHash] = '" + model.PasswordHash + "'" +
                        ",[Email] = '" + model.Email + "'" +
                        ",[SDT] = '" + model.SDT + "'" +
                        ",[DiaChi] = N'" + model.DiaChi + "'" +
                        ",[TongDiem] = " + model.TongDiem +
                        ",[NgayCapNhat] = GETDATE(), [TrangThai] = " + model.TrangThai +
                        " WHERE Id = " + model.Id;
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Delete a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int DeleteData(ModelVYC09 model)
        {
            var query = "DELETE FROM [VYC-09] " +
                        " WHERE Id = " + model.Id;
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }
    }
}